#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time : 2021-11-26 18:43
# @Author : huabao.tan
# @File : allFunction.py
# @Software: PyCharm
import json

from pypinyin import lazy_pinyin
import datetime
import random
import tkinter as tk
from tkinter import filedialog
from dateutil.parser import parse
from time import sleep
from selenium import webdriver
from selenium.webdriver.common.by import By
from tkinter.filedialog import askdirectory, askopenfilename
import os
import xlwt
import xlrd
from xlutils.copy import copy




def open_browser(txt):
    try:
        driver = getattr(webdriver, txt)()

    except Exception as e:
        print(e)
        driver = webdriver.Chrome()
    return driver


class Key:
    def __init__(self, txt):
        self.driver = open_browser(txt)

    def max(self):
        self.driver.maximize_window()

    # 访问URL
    def open(self, url):
        self.driver.get(url)

    # 查找元素
    def locator(self, txt):
        return self.driver.find_element_by_xpath(txt)

    # 输入
    def input(self, txt, txt1):
        self.locator(txt).send_keys(txt1)

    # 点击
    def click(self, txt):
        self.locator(txt).click()

    # 等待
    def wait(self, time_):
        sleep(time_)

    # 鼠标向下滚动
    def roll_max(self, txt):
        js = ("var q=document.documentElement.scrollTop='%s'".format(txt))
        self.driver.execute_script(js)
        self.wait(3)

    # 切换表单
    def switch(self, txt):
        self.driver.switch_to.frame(txt)

    # 等待
    def quint(self):
        self.driver.quit()


# 随机生成名字
def c_name():
    first_name = random.choice('谭')
    head1 = random.randint(0xb0, 0xf7)
    body1 = random.randint(0xa1, 0xf9)
    vaa = f'{head1:x}{body1:x}'
    secound_name = bytes.fromhex(vaa).decode('gb2312')
    head2 = random.randint(0xb0, 0xf7)
    body2 = random.randint(0xb0, 0xf7)
    vab = f'{head2:x}{body2:x}'
    last_name = bytes.fromhex(vab).decode('gb2312')
    name = first_name + secound_name + last_name
    return name


# 根据传入的名字生成拼音
def spell_name(name):
    a = ""
    b = lazy_pinyin(name)
    for c in b:
        a += c
    return a


# 随机生成手机号码
def generate_phone():
    sec = [3, 4, 5, 7, 8][random.randint(0, 4)]
    th = {
        3: random.randint(0, 9),
        4: [a for a in range(5, 10)][random.randint(0, 4)],
        5: [a for a in range(10) if a != 4][random.randint(0, 7)],
        7: [a for a in range(8)][random.randint(0, 7)],
        8: random.randint(0, 9)
    }[sec]
    end = random.randint(10000000, 99999999)
    phone = '1{0}{1}{2}'.format(sec, th, end)
    return phone


# 根据传入的名字生成邮箱
def email(name):
    n = spell_name(name)
    list = ['@thb.com', '@mzt.com', '@talebase.cocm']
    acc = random.choice(list)
    c = ""
    for a in n:
        c += a
    email = c + acc
    return email


# 随机生成性别
def create_sex():
    sex = random.choice('男女')
    return sex


# 随机生成年龄
def create_age():
    age = random.randint(18, 25)
    return str(age)


# 随机生成个人信息
def create_msg():
    name = c_name()
    msg = "姓名：" + name + "\n" + '身份证：' + creat_Idcard() + '\n' + "性别：" + create_sex() + "\n" + \
          "年龄：" + create_age() + "岁" + "\n" + "手机号码：" + generate_phone() + "\n" + "邮箱：" + email(name) + "\n" + \
          "时间：" + str(new_time()) + '\n'
    return msg


# 生成当前时间
def new_time():
    time = datetime.date.today()
    return time


# 评估关系
def assess():
    list = ['上级', '平级', '下属']
    acc = random.choice(list)
    return acc


# 图形化保存
def save_file():
    root = tk.Tk()
    root.withdraw()
    Fpath = filedialog.asksaveasfilename()
    return Fpath


# 图形化读取
def open_file():
    root = tk.Tk()
    root.withdraw()
    Fpath = filedialog.askopenfilename()
    return Fpath


def askdir():
    filename_path = askopenfilename()
    return filename_path


def choose_file():
    filename_path = tk.filedialog.askopenfilename(title="选择文件")
    e.set(filename_path)


# 随机生成身份证
def creat_Idcard():
    def creat_rand(n):
        if n < 10: return '000' + str(n)
        if n < 100: return '00' + str(n)
        if n < 1000: return '0' + str(n)
        return str(n)

    def creat_digit(m):
        if m < 10: return '0' + str(m)
        return str(m)

    acc = "452724"
    year = str(random.randint(1970, 2000))
    month = random.randint(1, 12)
    day = random.randint(1, 28)
    aca = random.randint(1, 9999)
    return acc + year + creat_digit(month) + creat_digit(day) + creat_rand(aca)


def education():
    list = ['初中及以下', '高中', '中专', '中技', '大专', '大学', '硕士研究生', '博士研究生']
    acc = random.choice(list)
    return acc


def college():
    list = ['清华大学', '北京大学', '南京大学', '四川大学', '重庆大学', '西南大学', '浙江大学', '北京师范大学', '兰州大学', '东北大学']
    acc = random.choice(list)
    return acc


def rank():
    list = ['正厅级', '副厅级', '正处级', '副处级', '正科级', '副科级', '三级职员', '四级职员']
    acc = random.choice(list)
    return acc


def duty():
    list = ['正职', '副职']
    acc = random.choice(list)
    return acc


def post():
    list = ['部门正职', '部门副职', '首席职员', '一级职员', '二级职员', '基层正职', '基层副职', '平台正职', '三级机构正职']
    acc = random.choice(list)
    return acc


def politics_status():
    list = ['中共党员', '共青团员', '民革党员', '中国民主同盟', '民建会员', '中国农工民主党']
    acc = random.choice(list)
    return acc


def foster():
    list = ['成熟', '可用', '关注']
    acc = random.choice(list)
    return acc


def position():
    list = ['董事长,党委书记', '董事,党委副书记,副总经理', '党委委员,纪委书记', '副总经理,党委委员', '总会计师,党委委员', '副总经理,党委委员',
            '副总经理,党委委员', '总工程师', '一级顾问', '三级顾问', '三级顾问', '三级顾问', '总经理,党委副书记', '总经理,党委副书记', '副总经理,党委委员',
            '党委书记,副总经理', '总经理,党委副书记', '党委书记,副总经理', '总经理助理', '总经理助理']
    acc = random.choice(list)
    return acc


def nation():
    list = ["蒙古族", "回族", "藏族", "苗族", "维吾尔族", "彝族", "壮族", "布依族",
            "白族", "朝鲜族", "侗族", "哈尼族", "哈萨克族", "满族", "土家族", "瑶族",
            "达斡尔族", "东乡族", "高山族", "景颇族", "柯尔克孜族", "拉祜族", "纳西族", "畲族",
            "傣族", "黎族", "傈僳族", "仫佬族", "羌族", "水族", "土族", "佤族",
            "阿昌族", "布朗族", "毛南族", "普米族", "撒拉族", "塔吉克族", "锡伯族", "仡佬族",
            "保安族", "德昂族", "俄罗斯族", "鄂温克族", "京族", "怒族", "乌孜别克族", "裕固族",
            "独龙族", "鄂伦春族", "赫哲族", "基诺族", "珞巴族", "门巴族", "塔塔尔族", "汉族"]
    acc = random.choice(list)
    return acc


def native_place():
    list = [
        "山东", "江苏", "安徽", "浙江", "福建", "上海", "广东", "广西", "海南", "湖北", "湖南", "河南", "江西", "北京", "天津", "河北", "山西", "内蒙古",
        "宁夏", "新疆", "青海", "陕西", "甘肃", "四川", "云南", "贵州", "西藏", "重庆", "辽宁", "吉林", "黑龙江"
    ]
    acc = random.choice(list)
    return acc


# 跟随身份证的信息生成出生日期
def birthday(time):
    acc = str(parse(time[6:14]))[0:10]
    return acc


def randomtimes(start, end, n, frmt="%Y-%m-%d"):
    stime = datetime.datetime.strptime(start, frmt)
    etime = datetime.datetime.strptime(end, frmt)
    time_datetime = [random.random() * (etime - stime) + stime for _ in range(n)]
    time_str = [t.strftime(frmt) for t in time_datetime]
    return time_str


def of_type():
    list = ['现单位', '职数归属', '二级党委委员归属', '三级党委委员归属', '兼职单位']
    acc = random.choice(list)
    return acc


def affiliation_type():
    list = ['省公司职数', '国网职数', '低职高配', '改革遗留', '职员', '二线']
    acc = random.choice(list)
    return acc


def unit():
    list = ["江苏电力", "助理副总师", "办公室", "发展部", "财务部", "安监部", "设备部", "营销部", "科技部", "建设部", "互联网部", "物资部", "宣传部",
            "审计部", "经法部", "组织部", "人资部", "离退休部", "后勤部", "党建部", "纪委办", "调控中心", "工会", "企协分会"]
    acc = random.choice(list)
    return acc


def specialty():
    list = ["哲学", "经济学", "财政学", "金融学", "法学", "外交学", "社会学", "治安学", "教育学", "体育教育", "汉语言文学", "新闻学", "历史学", "信息与计算科学",
            "物理学", "应用化学", "地理科学"]
    acc = random.choice(list)
    return acc


def degree():
    list = ['学士', '硕士', '博士']
    acc = random.choice(list)
    return acc


def learning_format():
    list = ['函授', '全日制', '脱产', '在职', '自考']
    acc = random.choice(list)
    return acc


def relation():
    list = ["妻子", "女儿", "父亲", "母亲", "哥哥", "儿子", "姐姐", "外甥女", "女婿", "丈夫", "妹妹", "弟弟", "儿媳", "岳父", "岳母", "姐夫", "妻哥", "嫂子",
            "内叔", "妻弟", "养女", "妹夫", "妻嫂", "妻兄", "连襟", "妹婿", "继母", "长女", "次女", "姨妈", "妻姐", "公公", "婆婆", "舅父", "伯父", "姑母"]
    acc = random.choice(list)
    return acc


def other_type():
    list = ["退休", "离休", "已故", "内退", "二线"]
    acc = random.choice(list)
    return acc


def nature():
    list = ['系统内', '系统外']
    acc = random.choice(list)
    return acc


def exchange():
    list = ['是', '否']
    acc = random.choice(list)
    return acc


# 遍历该目录下所有文件夹
def traverse():
    for dirpath, dirnames, filenames, in os.walk(r'C:\Users'):
        print(f"打开文件夹{dirpath}")
        if dirnames:
            print(dirnames)

        if filenames:
            print(filenames)

        print("====" * 24)


class ReadFromExcel:
    def __init__(self):
        self.filename = open_file()
        self.open_excel()

    def open_excel(self):
        self.workbook = xlrd.open_workbook(self.filename)

    # 获取所有sheet的内容
    def read_all_sheets_content(self):
        self.sheets = self.workbook.sheet_names()
        print(self.sheets)
        for i in self.sheets:
            self.mysheet = self.workbook.sheet_by_name(i)
            for j in range(self.mysheet.nrows):
                for k in range(self.mysheet.ncols):
                    print('%s：%d行%d列的内容为' % (i, j + 1, k + 1), self.mysheet.cell_value(j, k))

    # 通过sheet表名来获取sheet
    def get_sheet_by_name(self, sheetname):
        try:
            self.mysheetbook = self.workbook.sheet_by_name(sheetname)
        except Exception as e:
            msg = '%s不存在' % sheetname + str(e)
            raise Exception(msg)
        return self.mysheetbook

    # 通过index来获取sheet
    def get_sheet_by_index(self, index):
        try:
            self.mysheetbook = self.workbook.sheet_by_index(index)
        except Exception as e:
            msg = '%s超出index范围' % index + str(e)
            raise Exception(msg)
        return self.mysheetbook

    # 通过index来获取指定行的数据
    def read_sheet_row_content(self, index, row):
        self.mysheet = self.get_sheet_by_index(index)
        try:
            self.values = self.mysheet.row_values(row)
        except Exception as e:
            msg = '%d超出row范围' % row + str(e)
            raise Exception(msg)
        return self.values

    # 通过index来获取指定行列的数据
    def read_sheet_row_col_content(self, index, row, col):
        self.mysheet = self.get_sheet_by_index(index)
        try:
            self.value = self.mysheet.cell_value(row, col)
        except Exception as e:
            raise Exception('输入的%d行%d列的位置没有值' % (row, col))
        else:
            return self.value

    def read_first_row_content(self,index):
        mysheet = self.get_sheet_by_index(index)
        headers = []
        for k in range(mysheet.ncols):
            headers.append(mysheet.cell_value(0, k))
        return headers

    def read_data_to_dict(self, index):
        values = []
        mysheet = self.get_sheet_by_index(index)
        headers = self.read_first_row_content(index)
        for j in range(1, mysheet.nrows):
            rvalues = {}
            for k in range(mysheet.ncols):
                s = {}
                if \
                    headers[k] == self.read_sheet_row_col_content(0, 0, 0) or headers[k] == self.read_sheet_row_col_content(0, 0, 1) or headers[k] == self.read_sheet_row_col_content(0, 0, 2) \
                    or headers[k] == self.read_sheet_row_col_content(0, 0, 3) or headers[k] == self.read_sheet_row_col_content(0,0,4) or headers[k] == self.read_sheet_row_col_content(0, 0, 5) \
                    or headers[k] == self.read_sheet_row_col_content(0, 0, 6) or headers[k] == self.read_sheet_row_col_content(0,0,7) or headers[k] == self.read_sheet_row_col_content(0, 0,8) \
                    or headers[k] == self.read_sheet_row_col_content(0, 0, 9) or headers[k] == self.read_sheet_row_col_content(0,0,10) or headers[k] == self.read_sheet_row_col_content(0,0,11) \
                    or headers[k] == self.read_sheet_row_col_content(0, 0, 12) or headers[k] == self.read_sheet_row_col_content(0,0,13) or headers[k] == self.read_sheet_row_col_content(0,0,14) \
                    or headers[k] == self.read_sheet_row_col_content(0, 0, 15) or headers[k] == self.read_sheet_row_col_content(0,0,16) or headers[k] == self.read_sheet_row_col_content(0,0,17) \
                    or headers[k] == self.read_sheet_row_col_content(0, 0, 18) :
                    rvalues[headers[k]] = mysheet.cell_value(j, k)
                elif headers[k] == self.read_sheet_row_col_content(0, 0, 19) or headers[k] == self.read_sheet_row_col_content(0, 0, 20):
                    content = str(mysheet.cell_value(j, k))
                    contents = content.split(',')
                    for c in contents:
                        vs = str(c).split('=')
                        s[vs[0]] = vs[1]
                    rvalues[headers[k]] = s
                else:
                    pass
                values.append(rvalues)
            return values


if __name__ == '__main__':
    read = ReadFromExcel()
    list = read.read_data_to_dict(0)
    print(list)

